package util;

import java.io.InputStream;
import java.sql.*;


public class DBConnect {
	private Connection conn = null;

	private Statement stmt = null;

	private PreparedStatement prepstmt = null;
	
	public static final int AIGCIS_CONNECT = 0;//aigcis
	public static final int AIGSMS_CONNECT = 1;//aigSMS
	

	//默认的连接方式
	void init() {
		try {
			Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
			conn = DriverManager.getConnection("proxool.mysql");//proxool连接池
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/***
	 * 作用:带参数的proxool连接方式，通过conName读取proxool.xml选择要连接的数据库
	 * **/
	void init(String conName){
		try {
			Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
			conn = DriverManager.getConnection("proxool."+conName);//proxool连接池
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	

	/**
	 * 构造数据库的连接和访问类
	 */
	public DBConnect() throws SQLException {
		init();
		stmt = conn.createStatement();
	}

	public DBConnect(int resultSetType, int resultSetConcurrency)
			throws SQLException {
		init();
		stmt = conn.createStatement(resultSetType, resultSetConcurrency);
	}

	
	
/**
 * added by luyi 
 * @param connIndex
 * @param resultSetType
 * @param resultSetConcurrency
 * @throws SQLException
 */	
	
public DBConnect(int connIndex,int resultSetType, int resultSetConcurrency)throws SQLException {
//init();
switch (connIndex) {
case AIGCIS_CONNECT:
	init("mysql");
	break;
case AIGSMS_CONNECT:
	init("mysql_aigsms");
	break;
default:
	break;
}	
stmt = conn.createStatement(resultSetType, resultSetConcurrency);
}	
	
	
	
	
	
	
	/**
	 * 构造数据库的连接和访问类 预编译SQL语句
	 * 
	 * @param sql
	 *            SQL语句
	 */
	public DBConnect(String sql) throws SQLException {
		init();
		this.prepareStatement(sql);
	}

	public DBConnect(String sql, int resultSetType, int resultSetConcurrency)
			throws SQLException {
		init();
		this.prepareStatement(sql, resultSetType, resultSetConcurrency);
	}
	
	/***
	 * 作用：初始化连接
	 * **/
	public DBConnect(int connIndex, String sql) throws SQLException {
		switch (connIndex) {
		case AIGCIS_CONNECT:
			init("mysql");
			break;
		case AIGSMS_CONNECT:
			init("mysql_aigsms");
			break;
		default:
			break;
		}
		this.prepareStatement(sql);		
	}
	
	/***
	 * 作用；初始化连接
	 * **/
	public DBConnect(int connIndex,String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
		switch (connIndex) {
		case AIGCIS_CONNECT:
			init("mysql");
			break;
		case AIGSMS_CONNECT:
			init("mysql_aigsms");
			break;
		default:
			break;
		}
		this.prepareStatement(sql, resultSetType, resultSetConcurrency);
	}
		
	
	/**
	 * 返回连接
	 * 
	 * @return Connection 连接
	 */
	public Connection getConnection() {
		return conn;
	}

	/**
	 * PreparedStatement
	 * 
	 * @return sql 预设SQL语句
	 */
	public void prepareStatement(String sql) throws SQLException {
		prepstmt = conn.prepareStatement(sql);
	}

	public void prepareStatement(String sql, int resultSetType,
			int resultSetConcurrency) throws SQLException {
		prepstmt = conn.prepareStatement(sql, resultSetType,
				resultSetConcurrency);
	}

	/**
	 * 设置对应值
	 * 
	 * @param index
	 *            参数索引
	 * @param value
	 *            对应值
	 */
	public void setString(int index, String value) throws SQLException {
		prepstmt.setString(index, value);
	}

	public void setInt(int index, int value) throws SQLException {
		prepstmt.setInt(index, value);
	}

	public void setBoolean(int index, boolean value) throws SQLException {
		prepstmt.setBoolean(index, value);
	}

	public void setDate(int index, Date value) throws SQLException {
		prepstmt.setDate(index, value);
	}

	public void setLong(int index, long value) throws SQLException {
		prepstmt.setLong(index, value);
	}

	public void setFloat(int index, float value) throws SQLException {
		prepstmt.setFloat(index, value);
	}
	
	public void setTimeStamp(int index, Timestamp value) throws SQLException {
		prepstmt.setTimestamp(index, value);
	}
	public void setDouble(int index, double value) throws SQLException {
		prepstmt.setDouble(index, value);
	}
	public void setObject(int index, Object value)throws SQLException {
		prepstmt.setObject(index,value);
	}

	public void setBytes(int index, byte[] value) throws SQLException {
		prepstmt.setBytes(index, value);
	}

	public void setBinaryStream(int index, InputStream is, int length)
			throws SQLException {
		prepstmt.setBinaryStream(index, is, length);
	}

	public void clearParameters() throws SQLException {
		prepstmt.clearParameters();
		prepstmt = null;
	}

	/**
	 * 返回预设状态
	 */
	public PreparedStatement getPreparedStatement() {
		return prepstmt;
	}

	/**
	 * 返回状态
	 * 
	 * @return Statement 状态
	 */
	public Statement getStatement() {
		return stmt;
	}

	/**
	 * 执行SQL语句返回字段集
	 * 
	 * @param sql
	 *            SQL语句
	 * @return ResultSet 字段集
	 */
	public ResultSet executeQuery(String sql) throws SQLException {
		if (stmt != null) {
			return stmt.executeQuery(sql);
		} else
			return null;
	}

	public ResultSet executeQuery() throws SQLException {
		if (prepstmt != null) {
			return prepstmt.executeQuery();
		} else
			return null;
	}

	/**
	 * 执行SQL语句
	 * 
	 * @param sql
	 *            SQL语句
	 */
	public void executeUpdate(String sql) throws SQLException {
		if (stmt != null)
			stmt.executeUpdate(sql);
	}

	public void executeUpdate() throws SQLException {
		if (prepstmt != null)
			prepstmt.executeUpdate();
	}

	/**
	 * 关闭连接
	 */
	public void close() {
		try {
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
			if (prepstmt != null) {
				prepstmt.close();
				prepstmt = null;
			}
			if (conn != null) {
				conn.close();
				conn=null;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}

	}
	//事务回滚函数
	public void executeTransaction (String[] sqls)throws SQLException{
		
		init();
		try{
			stmt = conn.createStatement();
			// JAVA默认为TRUE,我们自己处理需要设置为FALSE,并且修改为手动提交,才可以调用rollback()函数
			conn.setAutoCommit(false);
			for(int i=0;i<sqls.length;i++){
				stmt.addBatch(sqls[i]);
			}
//			stmt.addBatch("insert into people values(078,'ding','duo')");
//			stmt.addBatch("insert into people values(30,'nokia','ddd')");
			stmt.executeBatch();
			// 事务提交
			conn.commit();
			// 设置为自动提交,改为TRUE
			conn.setAutoCommit(true);
		   }catch(SQLException se){
			  se.printStackTrace();
			  try{
				
				   if (conn != null) {
					   conn.rollback();
					   conn.setAutoCommit(true);
				      }
				 }catch(SQLException se1){
					se.printStackTrace();
				 }
				
			}finally{
				try {
					
					if (stmt != null) {
						stmt.close();
						stmt = null;
					}
					if (conn != null) {
						conn.close();
						conn = null;
					}
				} catch (SQLException se) {
					se.printStackTrace();
				}
			
		  }
	}
	
	//事务回滚函数
	public boolean executeTransactionflag(String[] sqls)throws SQLException{
		
		init();
		boolean flag = false;
		try{
			stmt = conn.createStatement();
			// JAVA默认为TRUE,我们自己处理需要设置为FALSE,并且修改为手动提交,才可以调用rollback()函数
			conn.setAutoCommit(false);
			for(int i=0;i<sqls.length;i++){
				stmt.addBatch(sqls[i]);
			}
			stmt.executeBatch();
			// 事务提交
			conn.commit();
			// 设置为自动提交,改为TRUE
			conn.setAutoCommit(true);
			flag = true;
		   }catch(SQLException se){
			  se.printStackTrace();
			  try{
				  flag = false;
				   if (conn != null) {
					   conn.rollback();
					   conn.setAutoCommit(true);
				      }
				 }catch(SQLException se1){
					se.printStackTrace();
				}
				 
		   }finally{
			    try {
					if (stmt != null) {
						stmt.close();
						stmt = null;
					}
					if (conn != null) {
						conn.close();
						conn = null;
					}
				} catch (SQLException see) {
					see.printStackTrace();
				}
			
		  }
	    
		   return flag;
	}
}